

 CREATE   PROC SP_MIGRATION_OF_END_USER
 
  @CUSTOMER_NAME VARCHAR(65),
  @END_CUSTOMER_NAME VARCHAR(65),
  @BILL_ADDRESS1 VARCHAR(50),
  @BILL_ADDRESS2 VARCHAR(50),
  @BILL_ADDRESS3 VARCHAR(50),
  @BILL_ADDRESS4 VARCHAR(50),
  @BILL_CITY VARCHAR(50),
  @BILL_STATE VARCHAR(50),
  @BILL_ZIP VARCHAR(50),
  @BILL_COUNTRY VARCHAR(50),
  @SHIP_ADDRESS1 VARCHAR(50),
  @SHIP_ADDRESS2 VARCHAR(50),
  @SHIP_ADDRESS3 VARCHAR(50),
  @SHIP_ADDRESS4 VARCHAR(50),
  @SHIP_CITY VARCHAR(50),
  @SHIP_STATE VARCHAR(50),
  @SHIP_ZIP VARCHAR(50),
  @SHIP_COUNTRY VARCHAR(50),
  @END_CUSTOMER_ID INT OUTPUT,
  @ERROR_CODE VARCHAR(200) OUTPUT
 
 
 AS
 BEGIN
 	DECLARE @DB_BILL_ADDRESS_ID INT
 	DECLARE @DB_SHIP_ADDRESS_ID INT
 	DECLARE @DB_CUSTOMER_ID INT
 	DECLARE @DB_END_CUSTOMER_ID INT
 	DECLARE @DB_C_TYPE_ID INT
 	DECLARE @DB_PRODUCT_ID INT
 	DECLARE @RELATIONSHIP_STATUS_ID INT 	
 
 	SELECT @DB_CUSTOMER_ID = CUSTOMER_ID FROM CUSTOMER WHERE C_NAME = @CUSTOMER_NAME
	SELECT @DB_C_TYPE_ID = C_TYPE_ID FROM  CUSTOMER_TYPES WHERE C_TYPE_NAME = 'END CUSTOMER' 	
 	SELECT @RELATIONSHIP_STATUS_ID = RELATIONSHIPSTATUS_ID FROM CUSTOMER_RELATIONSHIP_STATUS WHERE NAME = 'ACTIVE'			
 
 	IF ( @DB_CUSTOMER_ID IS NULL )
 		BEGIN	
 			SET @ERROR_CODE = 'Customer '+@CUSTOMER_NAME+' not available in daba base'
 		END
 
 	ELSE IF ( ( SELECT COUNT(*) FROM CUSTOMER WHERE C_NAME = @END_CUSTOMER_NAME ) > 0 )
 		BEGIN
 			SET @ERROR_CODE = 'Customer Name '+@END_CUSTOMER_NAME+' already exists in data base'		
	
			-- Insertion of End User as Customer of type End Customer		
			SELECT 	@DB_END_CUSTOMER_ID = CUSTOMER_ID FROM CUSTOMER WHERE C_NAME = @END_CUSTOMER_NAME

			IF ( ( SELECT COUNT(*) FROM CUSTOMER_TYPE_RELATIONSHIP WHERE  CUSTOMER_ID = @DB_END_CUSTOMER_ID AND C_TYPE_ID = @DB_C_TYPE_ID ) = 0  )		
			BEGIN
	 			INSERT INTO CUSTOMER_TYPE_RELATIONSHIP ( CUSTOMER_ID , C_TYPE_ID ) VALUES ( @DB_END_CUSTOMER_ID, @DB_C_TYPE_ID ) 
			END
 			
 
 			-- Insertion of RelationShip of End Customer with Given Customer
			IF ( ( SELECT COUNT(*) FROM CUSTOMER_ENDCUSTOMER_RELATIONSHIP WHERE PARENTCUSTOMER_ID = @DB_CUSTOMER_ID AND ENDCUSTOMER_ID = @DB_END_CUSTOMER_ID ) = 0 )
			BEGIN
	 			INSERT INTO CUSTOMER_ENDCUSTOMER_RELATIONSHIP ( PARENTCUSTOMER_ID, ENDCUSTOMER_ID, RELATIONSHIPSTATUS_ID )  VALUES ( @DB_CUSTOMER_ID, @DB_END_CUSTOMER_ID, @RELATIONSHIP_STATUS_ID )
			END
			
			SET @ERROR_CODE = +@ERROR_CODE+', so relationship established between customer and endcustomer' 
 
 		END
 	ELSE IF ( ( SELECT COUNT(*) FROM CUSTOMER WHERE C_NUMBER = @END_CUSTOMER_NAME ) > 0 )
 		BEGIN
 			SET @ERROR_CODE = 'Customer Number '+@END_CUSTOMER_NAME+' already exists in data base'		
 		END
 	ELSE
 		BEGIN		
 			
 			-- Insertion of billing address	
 			INSERT INTO ADDRESS ( ADDRESS_1, ADDRESS_2, ADDRESS_3, ADDRESS_4, CITY, STATE, ZIPCODE, COUNTRY  ) VALUES ( @BILL_ADDRESS1, @BILL_ADDRESS2 , @BILL_ADDRESS3, @BILL_ADDRESS4 , @BILL_CITY , @BILL_STATE , @BILL_ZIP , @BILL_COUNTRY  )
 				SET @DB_BILL_ADDRESS_ID = ( SELECT @@IDENTITY )
 		
 			
 			-- Insertion of shipping address
 			INSERT INTO ADDRESS ( ADDRESS_1, ADDRESS_2, ADDRESS_3, ADDRESS_4, CITY, STATE, ZIPCODE, COUNTRY  ) VALUES ( @SHIP_ADDRESS1, @SHIP_ADDRESS2 , @SHIP_ADDRESS3, @SHIP_ADDRESS4, @SHIP_CITY , @SHIP_STATE , @SHIP_ZIP , @SHIP_COUNTRY  )
 				SET @DB_SHIP_ADDRESS_ID = ( SELECT @@IDENTITY )
 		
 			--SELECT @DB_PRODUCT_ID = PRODUCT_ID FROM PRODUCT, PROCESS_STEP WHERE PRODUCT.PROCESSSTEP_ID = PROCESS_STEP.PROCESSSTEP_ID AND PART_NUMBER= @CAVIUM_PART_NUMBER AND PS_TYPE_NAME = 'FINISHED GOODS'	
 		
 		
 			-- Insertion of End User in Customer table
 			INSERT INTO CUSTOMER ( C_NAME, C_NUMBER, C_DEFAULT_BILLING_ADDRESS_ID, C_DEFAULT_SHIPPING_ADDRESS_ID, C_REGION, C_REP_OFFICE, C_CUSTOM_REQUIREMENTS, C_ECN_CHANGE_NOTIFICATION, C_DISABLED ) VALUES( @END_CUSTOMER_NAME, @END_CUSTOMER_NAME, @DB_BILL_ADDRESS_ID, @DB_SHIP_ADDRESS_ID, '', '', 1, 1, 1 )
 				SET @DB_END_CUSTOMER_ID = ( SELECT @@IDENTITY )
 		
 			
 
 			-- Insertion of End User as Customer of type End Customer		
 			INSERT INTO CUSTOMER_TYPE_RELATIONSHIP ( CUSTOMER_ID , C_TYPE_ID ) VALUES ( @DB_END_CUSTOMER_ID, @DB_C_TYPE_ID ) 
 			
 
 			-- Insertion of RelationShip of End Customer with Given Customer
 			INSERT INTO CUSTOMER_ENDCUSTOMER_RELATIONSHIP ( PARENTCUSTOMER_ID, ENDCUSTOMER_ID, RELATIONSHIPSTATUS_ID )  VALUES ( @DB_CUSTOMER_ID, @DB_END_CUSTOMER_ID, @RELATIONSHIP_STATUS_ID )  
 			
			SET @ERROR_CODE = 'End User '+@END_CUSTOMER_NAME+' Migrated Successfully'
 
 		END 

	SET @END_CUSTOMER_ID = @DB_END_CUSTOMER_ID
 
 END
